﻿using System;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using NLog;
using ctsu.Admin.Import;

namespace ctsu.Admin.Forms
{    
    public enum ImportType
    {
        NotSelected,
        Outpatient,
        Inpatient,
        UltraSound
    }

    public partial class FormImport : Form
    {
        private bool _doesImportNeedValidation = true;
        private string _fullFileName = string.Empty;
        private BindingSource _bindingSource1 = new BindingSource();
        private Logger _logger = LogManager.GetCurrentClassLogger();

        public ImportType _importType = ImportType.NotSelected;

        public FormImport()
        {
            InitializeComponent();
        }

        private void FormImport_Load(object sender, EventArgs e)
        {
            btnToDb.Enabled = false;
            btnRunRules.Enabled = false;
            //btnRunRulesRejected.Enabled = false;
        }
                
        private void btnBrowse_Click(object sender, EventArgs e)
        {            
            DialogResult result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                _importType = ImportType.NotSelected;
                _fullFileName = openFileDialog1.FileName;
                if (_fullFileName.Contains("Outpatient"))
                {
                    _importType = ImportType.Outpatient;

                }
                else if (_fullFileName.Contains("Inpatient"))
                {
                    _importType = ImportType.Inpatient;

                }
                else if (_fullFileName.Contains("UltraSound"))
                {
                    _importType = ImportType.UltraSound;

                }
                txtFileName.Text = openFileDialog1.SafeFileName;
                cboImportType.Text = _importType.ToString();
                chkVerified.Checked = false;
            }

        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (!chkVerified.Checked)
            {
                MessageBox.Show(this, "Please verify import type", "Verify Import Type", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }


            var sbWarning = new StringBuilder("Importing a new file will replace the current data in 'Imported file' and 'Rejected Rows' grids");
            sbWarning.Append(Environment.NewLine);
            sbWarning.Append("Press 'OK' to coninute");

            if (dgvImport.RowCount > 0)
            {
                if (MessageBox.Show(this, sbWarning.ToString(), "Warning", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Cancel)
                    return;
            }

            dgvImport.Rows.Clear();
            dgvImport.DataSource = null;
            dgvReject.Rows.Clear();
            dgvReject.DataSource = null;

            if (_fullFileName.Trim().Length == 0)
            {
                MessageBox.Show(this, "No file selected","File Import",MessageBoxButtons.OK,MessageBoxIcon.Information );
                return;
            }

            btnImport.Enabled = false;
            switch (_importType)
            {
                case ImportType.Outpatient:
                    var linesOut = ImportHelpers.GetOutpatientImportFile(_fullFileName);
                    if (linesOut == null)
                    {
                        btnImport.Enabled = true;
                        return;
                    }
                    dgvImport.DataSource = linesOut;
                    break;
                case ImportType.Inpatient:
                    var linesIn = ImportHelpers.GetInpatientImportFile(_fullFileName);
                    if (linesIn == null)
                    {
                        btnImport.Enabled = true;
                        return;
                    }
                    dgvImport.DataSource = linesIn;
                    break;
                case ImportType.UltraSound:
                    var linesUs = ImportHelpers.GetUltraSoundImportFile(_fullFileName);
                    if (linesUs == null)
                    {
                        btnImport.Enabled = true;
                        return;
                    }
                    dgvImport.DataSource = linesUs;
                    break;
            }
            
            dgvImport.AllowUserToDeleteRows = true;
            btnToDb.Enabled = false;
            //btnRunRulesRejected.Enabled = false;
            
            if(dgvImport.RowCount == 0)
            {
                sbWarning = new StringBuilder("There were no rows imported.  This could be casued by a using a delimiter other than '!'.");
                
                MessageBox.Show(this, sbWarning.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            }
            else
            {
                btnRunRules.Enabled = true;
                AdjustColumnOrder();
            }
            btnImport.Enabled = true;
        }

        private void AdjustColumnOrder()
        {
            dgvImport.Columns["Calendar"].DisplayIndex = 0;
            dgvImport.Columns["SubjectLine"].DisplayIndex = 1;
            dgvImport.Columns["PatLastName"].DisplayIndex = 2;
            dgvImport.Columns["PatFirstName"].DisplayIndex = 3;
            dgvImport.Columns["PatMrn"].DisplayIndex = 4;
            dgvImport.Columns["PatDob"].DisplayIndex = 5;
            dgvImport.Columns["PatGender"].DisplayIndex = 6;
            dgvImport.Columns["Protocol"].DisplayIndex = 7;
            dgvImport.Columns["CcNum"].DisplayIndex = 8;
            dgvImport.Columns["Location"].DisplayIndex = 9;
            dgvImport.Columns["Line"].Visible = false;
        }

        private void btnToDb_Click(object sender, EventArgs e)
        {
            this.Enabled = false;
           
            switch (_importType)
            {
                case ImportType.Outpatient:
                    ImportToDbOutpatient();
                    break;
                case ImportType.Inpatient:
                    ImportToDbInpatient();
                    break;
                case ImportType.UltraSound:
                    ImportToDbUltrasound();
                    break;
            }

            btnToDb.Enabled = false;
            this.Enabled = true;
        }

        private void ImportToDbInpatient()
        {
            string timeOut;
            string timeIn;
            string ap;
            int hour = 0;
            
            var lines = dgvImport.DataSource as SortableBindingList<ImportColumnsInpatient>;

            int count = 0;
            int total = lines.Count;

            int patientID = 0;
            int protocolID = 0;
            foreach (var line in lines)
            {
                count++;
                lblProgress.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();

                patientID = GetPatientID(line.PatMrn);
                if (patientID == 0)
                {
                    patientID = AddPatientToDb(line);
                }
                if (patientID == -1)
                {
                    //reject error;
                    continue;
                }
                protocolID = GetProtocolID(line.Protocol);
                if (protocolID == -1)
                {
                    //reject error;
                    continue;
                }
                if (protocolID == 0)
                {
                    //not found;
                    continue;
                }

                //time in
                string[] sDtParts = line.AdmitDate.Split(' ');
                DateTime dtStart = DateTime.Parse(sDtParts[0]);

                string timePart = sDtParts[1];
                string[] sTimeParts = timePart.Split(':');
                hour = int.Parse(sTimeParts[0]);

                string ampm = sDtParts[2];
                if (ampm == "PM")
                {
                    ap = "p";
                }
                else //am
                {
                    ap = "a";
                }

                timeIn = hour.ToString("00") + ":" + sTimeParts[1] + ap;
                
                //time out
                sDtParts = line.DischargeDate.Split(' ');
                DateTime dtEnd = DateTime.Parse(sDtParts[0]);

                timePart = sDtParts[1];
                sTimeParts = timePart.Split(':');
                hour = int.Parse(sTimeParts[0]);

                ampm = sDtParts[2];
                if (ampm == "PM")
                {
                    ap = "p";
                }
                else //am
                {
                    ap = "a";
                }

                int roomId = ImportHelpers.GetRoomId(line.Location.ToUpper());

                timeOut = hour.ToString("00") + ":" + sTimeParts[1] + ap;
                
                int site;
                switch (line.Calendar)
                {
                    case "CTC-GCRC":
                        site = 4;
                        break;
                    case "GCRC-9A":
                        site = 2;
                        break;
                    case "GCRC-9B":
                        site = 3;
                        break;
                    default:
                        site = 5;
                        break;
                }

                
                ImportInpatientToDb(line, patientID, protocolID, dtStart, dtEnd, timeIn, timeOut, site, roomId);
                ImportColumnsJournal lineJ = ConvertLineToJournal(line);
                AddImportJournalToDb(lineJ, 2, 1);
            }
        }

        private void ImportToDbOutpatient()
        {
            string timeOut;
            string timeIn;
            string timeOutM;
            string timeInM;
            string ap;
            int hour = 0;
            int hourM = 0;

            var lines = dgvImport.DataSource as SortableBindingList<ImportColumnsOutpatient>;

            int count = 0;
            int total = lines.Count;

            int patientID = 0;
            int protocolID = 0;
            foreach (var line in lines)
            {
                count++;
                lblProgress.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();

                patientID = GetPatientID(line.PatMrn);
                if (patientID == 0)
                {
                    patientID = AddPatientToDb(line);
                }
                if (patientID == -1)
                {
                    //reject error;
                    continue;
                }
                protocolID = GetProtocolID(line.Protocol);
                if (protocolID == -1)
                {
                    //reject error;
                    continue;
                }
                if (protocolID == 0)
                {
                    //not found;
                    continue;
                }

                //time in
                string[] sDtParts = line.DateStart.Split(' ');
                DateTime dtStart = DateTime.Parse(sDtParts[0]);
                
                string timePart = sDtParts[1];
                string[] sTimeParts = timePart.Split(':');
                hour = int.Parse(sTimeParts[0]);
                hourM = hour;
                
                string ampm = sDtParts[2];
                if (ampm == "PM")
                {
                    ap = "p";
                    if (hour < 12)
                        hourM = hour + 12;
                }
                else //am
                {
                    ap = "a";
                    if (hour == 12)
                        hourM = 0;
                }

                timeIn = hour.ToString("00") + ":" + sTimeParts[1] + ap;
                timeInM = hourM.ToString("00") + ":" + sTimeParts[1];

                //time out
                sDtParts = line.DateEnd.Split(' ');
                DateTime dtEnd = DateTime.Parse(sDtParts[0]);

                timePart = sDtParts[1];
                sTimeParts = timePart.Split(':');
                hour = int.Parse(sTimeParts[0]);
                hourM = hour;

                ampm = sDtParts[2];
                if (ampm == "PM")
                {
                    ap = "p";
                    if (hour < 12)
                        hourM = hour + 12;
                }
                else //am
                {
                    ap = "a";
                    if (hour == 12)
                        hourM = 0;
                }

                int roomId = ImportHelpers.GetRoomId(line.Location.ToUpper());

                timeOut = hour.ToString("00") + ":" + sTimeParts[1] + ap;
                timeOutM = hourM.ToString("00") + ":" + sTimeParts[1];

                int site;
                switch (line.Calendar)
                {
                    case "CTC-GCRC":
                        site = 4;
                        break;
                    case "GCRC-9A":
                        site = 2;
                        break;
                    case "GCRC-9B":
                        site = 3;
                        break;
                    default:
                        site = 5;
                        break;
                }




                ImportOutpatientToDb(line, patientID, protocolID, dtStart, dtEnd, timeIn, timeOut, timeInM, timeOutM, site, roomId);
                ImportColumnsJournal lineJ = ConvertLineToJournal(line);
                AddImportJournalToDb(lineJ, 1, 1);
            }
        }

        private void ImportToDbUltrasound()
        {
            string timeOut;
            string timeIn;
            string timeOutM;
            string timeInM;
            string ap;
            int hour = 0;
            int hourM = 0;

            var lines = dgvImport.DataSource as SortableBindingList<ImportColumnsUltraSound>;

            int count = 0;
            int total = lines.Count;

            int patientID = 0;
            int protocolID = 0;
            foreach (var line in lines)
            {
                count++;
                lblProgress.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();

                patientID = GetPatientID(line.PatMrn);
                if (patientID == 0)
                {
                    patientID = AddPatientToDb(line);
                }
                if (patientID == -1)
                {
                    //reject error;
                    continue;
                }
                protocolID = GetProtocolID(line.Protocol);
                if (protocolID == -1)
                {
                    //reject error;
                    continue;
                }
                if (protocolID == 0)
                {
                    //not found;
                    continue;
                }

                //time in
                string[] sDtParts = line.DateStart.Split(' ');
                DateTime dtStart = DateTime.Parse(sDtParts[0]);

                string timePart = sDtParts[1];
                string[] sTimeParts = timePart.Split(':');
                hour = int.Parse(sTimeParts[0]);
                hourM = hour;

                string ampm = sDtParts[2];
                if (ampm == "PM")
                {
                    ap = "p";
                    if (hour < 12)
                        hourM = hour + 12;
                }
                else //am
                {
                    ap = "a";
                    if (hour == 12)
                        hourM = 0;
                }

                timeIn = hour.ToString("00") + ":" + sTimeParts[1] + ap;
                timeInM = hourM.ToString("00") + ":" + sTimeParts[1];

                //time out
                sDtParts = line.DateEnd.Split(' ');
                DateTime dtEnd = DateTime.Parse(sDtParts[0]);

                timePart = sDtParts[1];
                sTimeParts = timePart.Split(':');
                hour = int.Parse(sTimeParts[0]);
                hourM = hour;

                ampm = sDtParts[2];
                if (ampm == "PM")
                {
                    ap = "p";
                    if (hour < 12)
                        hourM = hour + 12;
                }
                else //am
                {
                    ap = "a";
                    if (hour == 12)
                        hourM = 0;
                }

                int roomId = ImportHelpers.GetRoomId(line.Location.ToUpper());

                timeOut = hour.ToString("00") + ":" + sTimeParts[1] + ap;
                timeOutM = hourM.ToString("00") + ":" + sTimeParts[1];

                int site;
                switch (line.Calendar)
                {
                    case "CTC-GCRC":
                        site = 4;
                        break;
                    case "GCRC-9A":
                        site = 2;
                        break;
                    case "GCRC-9B":
                        site = 3;
                        break;
                    default:
                        site = 5;
                        break;
                }




                ImportUltrasoundToDb(line, patientID, protocolID, dtStart, dtEnd, timeIn, timeOut, timeInM, timeOutM, site, roomId);
                ImportColumnsJournal lineJ = ConvertLineToJournal(line);
                AddImportJournalToDb(lineJ, 3, 1);
            }
        }

        private ImportColumnsJournal ConvertLineToJournal(ImportColumnsInpatient line)
        {
            var linej = new ImportColumnsJournal();
            linej.Bp = line.Bp;
            linej.Calendar = line.Calendar;
            linej.CcNum = line.CcNum;
            linej.Consent = line.Consent;
            linej.CostCenter = line.CostCenter;
            linej.EkgCci = line.EkgCci;
            linej.EkgStudy = line.EkgStudy;
            linej.Inpatient = line.Inpatient;
            linej.Lab = line.Lab;
            linej.Location = line.Location;
            linej.PatDob = line.PatDob;
            linej.PatFirstName = line.PatFirstName;
            linej.PatLastName = line.PatLastName;
            linej.PatGender = line.PatGender;
            linej.PatMrn = line.PatMrn;
            linej.Phlebotomy = line.Phlebotomy;
            linej.Physical = line.Physical;
            linej.Pi = line.Pi;
            linej.Processing = line.Processing;
            linej.Protocol = line.Protocol;
            linej.Np = line.Np;
            linej.Nurse = line.Nurse;
            linej.Ogit = line.Ogit;
            linej.Outpatient = line.Outpatient;
            linej.Room = line.Room;
            linej.ScEmail = line.ScEmail;
            linej.ScFirstName = line.ScFirstName;
            linej.ScLastName = line.ScLastName;
            linej.Status = line.Status;
            linej.SubjectLine = line.SubjectLine;
            linej.Ultrasound = line.Ultrasound;
            linej.Vitals = line.Vitals;
            linej.Xrf = line.Xrf;
            linej.AdmitDate = line.AdmitDate;
            linej.DischargeDate = line.DischargeDate;
            return linej;
        }

        private ImportColumnsJournal ConvertLineToJournal(ImportColumnsOutpatient line)
        {
            var linej = new ImportColumnsJournal();
            linej.Bp = line.Bp;
            linej.Calendar = line.Calendar;
            linej.CcNum = line.CcNum;
            linej.Consent = line.Consent;
            linej.CostCenter = line.CostCenter;
            linej.EkgCci =line.EkgCci;
            linej.EkgStudy = line.EkgStudy;
            linej.Inpatient = line.Inpatient;
            linej.Lab = line.Lab;
            linej.Location = line.Location;
            linej.PatDob = line.PatDob;
            linej.PatFirstName = line.PatFirstName;
            linej.PatLastName = line.PatLastName;
            linej.PatGender = line.PatGender;
            linej.PatMrn = line.PatMrn;
            linej.Phlebotomy = line.Phlebotomy;
            linej.Physical = line.Physical;
            linej.Pi = line.Pi;
            linej.Processing = line.Processing;
            linej.Protocol = line.Protocol;
            linej.Np = line.Np;
            linej.Nurse = line.Nurse;
            linej.Ogit = line.Ogit;
            linej.Outpatient = line.Outpatient;
            linej.Room = line.Room;
            linej.ScEmail = line.ScEmail;
            linej.ScFirstName = line.ScFirstName;
            linej.ScLastName = line.ScLastName;
            linej.Status = line.Status;
            linej.SubjectLine = line.SubjectLine;
            linej.Ultrasound = line.Ultrasound;
            linej.Vitals =  line.Vitals;
            linej.Xrf = line.Xrf;
            linej.DateStart = line.DateStart;
            linej.DateEnd = line.DateEnd;
            linej.VisitLength = line.VisitLength;
            return linej;
        }

        private ImportColumnsJournal ConvertLineToJournal(ImportColumnsUltraSound line)
        {
            var linej = new ImportColumnsJournal();
            linej.Bp = line.Bp;
            linej.Calendar = line.Calendar;
            linej.CcNum = line.CcNum;
            linej.Consent = line.Consent;
            linej.CostCenter = line.CostCenter;
            linej.EkgCci = line.EkgCci;
            linej.EkgStudy = line.EkgStudy;
            linej.Inpatient = line.Inpatient;
            linej.Lab = line.Lab;
            linej.Location = line.Location;
            linej.PatDob = line.PatDob;
            linej.PatFirstName = line.PatFirstName;
            linej.PatLastName = line.PatLastName;
            linej.PatGender = line.PatGender;
            linej.PatMrn = line.PatMrn;
            linej.Phlebotomy = line.Phlebotomy;
            linej.Physical = line.Physical;
            linej.Pi = line.Pi;
            linej.Processing = line.Processing;
            linej.Protocol = line.Protocol;
            linej.Np = line.Np;
            linej.Nurse = line.Nurse;
            linej.Ogit = line.Ogit;
            linej.Outpatient = line.Outpatient;
            linej.Room = line.Room;
            linej.ScEmail = line.ScEmail;
            linej.ScFirstName = line.ScFirstName;
            linej.ScLastName = line.ScLastName;
            linej.Status = line.Status;
            linej.SubjectLine = line.SubjectLine;
            linej.Ultrasound = line.Ultrasound;
            linej.Vitals = line.Vitals;
            linej.Xrf = line.Xrf;
            linej.DateStart = line.DateStart;
            linej.DateEnd = line.DateEnd;
            linej.VisitLength = line.VisitLength;
            linej.Pwv = line.Pwv;
            linej.Pat = line.Pat;
            linej.Carotid = line.Carotid;
            linej.Brachial = line.Brachial;
            linej.Echo = line.Echo;
            linej.LowerExtremity = line.LowerExtremity;
            return linej;
        }

        private void AddImportJournalToDb(ImportColumnsJournal line, int importType, int importStatus)
        {
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = ("AddImportJournal");

                    SqlParameter param = new SqlParameter("@importType", importType);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@calendar", line.Calendar);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@subjectLine", line.SubjectLine);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patLastName", line.PatFirstName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patFirstName", line.PatLastName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patMrn", line.PatMrn);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patDob", line.PatDob);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patGender", line.PatGender);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@protocol", line.Protocol);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCLastName", line.ScLastName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCFirstName", line.ScFirstName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCEmail", line.ScEmail);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pI", line.Pi);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@cCNum", line.CcNum);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@location", line.Location);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@status", line.Status);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@costCenter", line.CostCenter);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nurse", line.Nurse);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@phlebotomy", line.Phlebotomy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@processing", line.Processing);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@room", line.Room);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_Study", line.EkgStudy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_CCI", line.EkgCci);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@vitals", line.Vitals);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@ultrasound", line.Ultrasound);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dynamap", line.Dynamap);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@bP", line.Bp);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@physical", line.Physical);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@xRF", line.Xrf);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@consent", line.Consent);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@inpatient", line.Inpatient);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@outpatient", line.Outpatient);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lab", line.Lab);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@oGIT", line.Ogit);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nP", line.Np);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dateStart", line.DateStart);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dateEnd", line.DateEnd);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@visitLength", line.VisitLength);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@admitDate", line.AdmitDate);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dischargeDate", line.DischargeDate);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@bedDays", line.BedDays);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pWV", line.Pwv);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pAT", line.Pat);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@carotid", line.Carotid);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@brachial", line.Brachial);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@echo", line.Echo);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lower_Extremity", line.LowerExtremity);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@rejectReason", line.RejectReason);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@ImportStatus", importStatus);
                    cmd.Parameters.Add(param);
                    conn.Open();

                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                }
            }
        }

        private void ImportOutpatientToDb(ImportColumnsOutpatient line, int patient, int protocol, DateTime dtStart, DateTime dtEnd, string timeIn, string timeOut, string timeInM, string timeOutM, int site, int roomId)
        {
            int importType = 1;
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = ("ImportOutpatientVist");

                    var param = new SqlParameter("@confirmation", line.CcNum);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patient", patient);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@protocol", protocol);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@visitDate", DateTime.Parse(line.DateStart));
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@manualDuration", false);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@timeIn", timeIn);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@timeOut", timeOut);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@duration", line.VisitLength);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@category", 1);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@site", site);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@createdBy", "import");
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@createdWhen", DateTime.Now);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@room1", roomId);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@rm1TimeIn", timeInM);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@rm1TimeOut", timeOutM);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@outpatientVisitType", 1);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nurse", line.Nurse);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@phlebotomy", line.Phlebotomy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@processing", line.Processing);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@room", line.Room);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_Study", line.EkgStudy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_CCI", line.EkgCci);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@vitals", line.Vitals);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@ultrasound", line.Ultrasound);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dynamap", line.Dynamap);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@bP", line.Bp);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@physical", line.Physical);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@xRF", line.Xrf);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@consent", line.Consent);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lab", line.Lab);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@oGIT", line.Ogit);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nP", line.Np);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@costCenter", line.CostCenter);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCLastName", line.ScLastName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCFirstName", line.ScFirstName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCEmail", line.ScEmail);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@importType", importType);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pI", line.Pi);
                    cmd.Parameters.Add(param);
                    conn.Open();

                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                    
                }
            }
        }

        private void ImportUltrasoundToDb(ImportColumnsUltraSound line, int patient, int protocol, DateTime dtStart, DateTime dtEnd, string timeIn, string timeOut, string timeInM, string timeOutM, int site, int roomId)
        {
            int importType = 1;
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = ("ImportUltrasoundVist");

                    var param = new SqlParameter("@confirmation", line.CcNum);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patient", patient);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@protocol", protocol);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@visitDate", DateTime.Parse(line.DateStart));
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@manualDuration", false);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@timeIn", timeIn);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@timeOut", timeOut);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@duration", line.VisitLength);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@category", 1);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@site", site);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@createdBy", "import");
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@createdWhen", DateTime.Now);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@room1", roomId);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@rm1TimeIn", timeInM);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@rm1TimeOut", timeOutM);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@outpatientVisitType", 1);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nurse", line.Nurse);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@phlebotomy", line.Phlebotomy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@processing", line.Processing);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@room", line.Room);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_Study", line.EkgStudy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_CCI", line.EkgCci);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@vitals", line.Vitals);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@ultrasound", line.Ultrasound);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dynamap", line.Dynamap);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@bP", line.Bp);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@physical", line.Physical);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@xRF", line.Xrf);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@consent", line.Consent);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lab", line.Lab);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@oGIT", line.Ogit);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nP", line.Np);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@costCenter", line.CostCenter);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCLastName", line.ScLastName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCFirstName", line.ScFirstName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCEmail", line.ScEmail);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@importType", importType);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pI", line.Pi);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pWV", line.Pwv);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pAT", line.Pat);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@carotid", line.Carotid);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@brachial", line.Brachial);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@echo", line.Echo);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lower_Extremity", line.LowerExtremity);
                    cmd.Parameters.Add(param);
                    conn.Open();

                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                }
            }
        }
        
        private void ImportInpatientToDb(ImportColumnsInpatient line, int patient, int protocol, DateTime dtStart, DateTime dtEnd, string timeIn, string timeOut, int site, int roomId)
        {
            int importType = 1;
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = ("ImportInpatientVist");

                    var param = new SqlParameter("@confirmation", line.CcNum);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@patient", patient);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@protocol", protocol);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@visitDate", DateTime.Parse(line.AdmitDate));
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dischargeDate", DateTime.Parse(line.DischargeDate));
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@timeIn", timeIn);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@timeOut", timeOut);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@category", 1);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@site", site);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@createdBy", "import");
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@createdWhen", DateTime.Now);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@admissionVisitType", 1);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nurse", line.Nurse);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@phlebotomy", line.Phlebotomy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@processing", line.Processing);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@room", line.Room);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_Study", line.EkgStudy);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@eKG_CCI", line.EkgCci);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@vitals", line.Vitals);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@ultrasound", line.Ultrasound);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dynamap", line.Dynamap);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@bP", line.Bp);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@physical", line.Physical);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@xRF", line.Xrf);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@consent", line.Consent);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lab", line.Lab);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@oGIT", line.Ogit);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@nP", line.Np);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@costCenter", line.CostCenter);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCLastName", line.ScLastName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCFirstName", line.ScFirstName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@sCEmail", line.ScEmail);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@importType", importType);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@pI", line.Pi);
                    cmd.Parameters.Add(param);
                    conn.Open();

                    cmd.ExecuteNonQuery();
                    
                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                }
            }
        }
        
        private int AddPatientToDb(ImportColumnsBase line)
        {            
            string mrn = "";
            string otherID = "";
            int iTemp = 0;
            int iGender = 0;

            if (int.TryParse(line.PatMrn, out iTemp))
                mrn = line.PatMrn;
            else
                otherID = line.PatMrn;

            switch (line.PatGender)
            {
                case "M":
                    iGender = 1;
                    break;
                case "F":
                    iGender = 2;
                    break;
                case "MF":
                    iGender = 3;
                    break;
                case "FM":
                    iGender = 4;
                    break;

            }

            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = ("AddPatient");

                    SqlParameter param = new SqlParameter("@Identity", System.Data.SqlDbType.Int, 0, "ID");
                    param.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@firstName", line.PatFirstName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@lastName", line.PatLastName);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@dob", line.PatDob);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@gender", iGender);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@mrn", mrn);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@otherPatientID", otherID);
                    cmd.Parameters.Add(param);
                    conn.Open();

                    cmd.ExecuteNonQuery();
                    
                    return (int)cmd.Parameters["@Identity"].Value;
                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                    return -1;
                }
            }

        }

        private int GetPatientID(string patMrn)
        {
            int iTemp = 0;
            string storedProcedure = "GetPatientIdByMrn";

            if (! int.TryParse(patMrn, out iTemp))
                storedProcedure = "GetPatientIdByOtherId";
            
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    //throw new Exception("Test error");
                    var cmd = new SqlCommand("", conn)
                                  {CommandType = System.Data.CommandType.StoredProcedure, CommandText = storedProcedure};
                    SqlParameter param;
                    param = storedProcedure == "GetPatientIdByMrn" ? new SqlParameter("@mrn", patMrn) : new SqlParameter("@otherPatientID", patMrn);
                    cmd.Parameters.Add(param);

                    conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if (obj == null)
                        return 0;
                    if (obj == DBNull.Value)
                        return 0;

                    return (Int32)obj;
                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                    return -1;    
                }
            }
            
        }

        private int ConfirmationIsDuplicate(string ccNum, ImportType importType)
        {
            string storedProcedure = "";
            switch (importType)
            {
                case ImportType.Outpatient:
                    storedProcedure = "IsConfirmationInDbOutpatient";
                    break;

                case ImportType.Inpatient:
                    storedProcedure = "IsConfirmationInDbInpatient";
                    break;

                case ImportType.UltraSound:
                    storedProcedure = "IsConfirmationInDbUltrasound";
                    break;
            }
            
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    //throw new Exception("Test error");
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = storedProcedure;
                    SqlParameter param = new SqlParameter("@confirmation", ccNum);
                    cmd.Parameters.Add(param);

                    conn.Open();
                    int id = (Int32)cmd.ExecuteScalar();
                    return id;
                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                    return -1;
                }
            }
        }

        private int GetProtocolID(string protocol)
        {
            
            String strConn = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    //throw new Exception("Test error");
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = ("GetProtocolID");
                    SqlParameter param = new SqlParameter("@protocolNumber", protocol);
                    cmd.Parameters.Add(param);
                    
                    conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if (obj == null)
                        return 0;
                    
                    return (Int32)obj;
                }
                catch (Exception ex)
                {
                    _logger.Error(ex);
                    return -1;
                }
            }

        }

        private void btnRunRules_Click(object sender, EventArgs e)
        {
            btnRunRules.Enabled = false;
            this.Enabled = false;
            
            switch (_importType)
            {
                case ImportType.Outpatient:
                    RunRulesOutpatient();
                    break;
                case ImportType.Inpatient:
                    RunRulesInpatient();
                    break;
                case ImportType.UltraSound:
                    RunRulesUltraSound();
                    break;
            }
            
            btnRunRules.Enabled = true;
            this.Enabled = true;

            _doesImportNeedValidation = false;
        }

        private void RunRulesOutpatient()
        {
            DateTime dtResult = new DateTime();
            DateTime dtStart = new DateTime();

            var lines = dgvImport.DataSource as SortableBindingList<ImportColumnsOutpatient>;
            var acceptLines = new SortableBindingList<ImportColumnsOutpatient>();
            var rejList = new SortableBindingList<ImportColumnsOutpatientReject>();
            var rejLine = new ImportColumnsOutpatientReject();

            int count = 0;
            int total = lines.Count;
            Cursor.Current = Cursors.WaitCursor;

            foreach (var line in lines)
            {
                count++;
                lblProgress.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();
                rejLine = new ImportColumnsOutpatientReject();

                if (line.CcNum.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No confirmation number";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    int retVal = ConfirmationIsDuplicate(line.CcNum, ImportType.Outpatient);
                    if (retVal != 0)
                    {
                        if (retVal == 1)
                            rejLine.RejectReason = "Confirmation number already exists in the database";
                        else
                            rejLine.RejectReason = "ERROR:in Procedure - ConfirmationIsDuplicate";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }

                if (line.PatFirstName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient first name";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatLastName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient last name";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                string patientGender = line.PatGender.Trim();
                if (patientGender.Length == 0)
                {
                    rejLine.RejectReason = "No patient gender";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (!((patientGender == "M") || (patientGender == "MF") || (patientGender == "F") || (patientGender == "FM")))
                {
                    rejLine.RejectReason = "Invalid patient gender";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatDob.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient DOB";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (line.PatDob == "01/01/1900")
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (!DateTime.TryParse(line.PatDob, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The date of birth can't be later than today's date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateStart.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit Start Date";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateStart, out dtStart))
                    {
                        rejLine.RejectReason = "Invalid Visit Start Date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than today's date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateEnd.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit End Date";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateEnd, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Visit End Date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit end date can't be later than today's date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(dtResult) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than the end date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Protocol.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Protocol number";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (ProtocolNotFound(line.Protocol))
                    {
                        rejLine.RejectReason = "Protocol not found in database";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Location.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No location";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }

                //todo - more validation on location
                string mrn = ImportHelpers.ValidateMrn(line);
                line.PatMrn = mrn;

                acceptLines.Add(line);
            }
            dgvImport.DataSource = acceptLines;
            if (acceptLines.Count > 0)
                btnToDb.Enabled = true;
            else
            {
                btnToDb.Enabled = false;
            }

            SortableBindingList<ImportColumnsOutpatientReject> rejListCurrent; 
            //keep current rejected rows and add to the new ones
            if(dgvReject.RowCount > 0)
            {
                rejListCurrent = dgvReject.DataSource as SortableBindingList<ImportColumnsOutpatientReject>;
                foreach (var item in rejListCurrent)
                {
                    rejList.Add(item);
                }
            }
            dgvReject.DataSource = rejList;
            dgvReject.Columns[0].Frozen = true;
            Cursor.Current = Cursors.Default;
            
        }

        private void RunRulesOutpatientReject()
        {
            DateTime dtResult = new DateTime();
            DateTime dtStart = new DateTime();
            var lines = dgvReject.DataSource as SortableBindingList<ImportColumnsOutpatientReject>;
            if (lines == null)
                return;
            var acceptLines = dgvImport.DataSource as SortableBindingList<ImportColumnsOutpatient>;
            var rejList = new SortableBindingList<ImportColumnsOutpatientReject>();
            var rejLine = new ImportColumnsOutpatientReject();

            int count = 0;
            int total = lines.Count;
            Cursor.Current = Cursors.WaitCursor;

            foreach (var line in lines)
            {
                count++;
                lblProgress2.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();
                rejLine = new ImportColumnsOutpatientReject();
                
                if (line.CcNum.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No confirmation number";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    int retVal = ConfirmationIsDuplicate(line.CcNum, ImportType.Outpatient);
                    if (retVal != 0)
                    {
                        if (retVal == 1)
                            rejLine.RejectReason = "Confirmation number already exists in the database";
                        else
                            rejLine.RejectReason = "ERROR:in Procedure - ConfirmationIsDuplicate";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }

                if (line.PatFirstName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient first name";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatLastName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient last name";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                string patientGender = line.PatGender.Trim();
                if (patientGender.Length == 0)
                {
                    rejLine.RejectReason = "No patient gender";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (!((patientGender == "M") || (patientGender == "MF") || (patientGender == "F") || (patientGender == "FM")))
                {
                    rejLine.RejectReason = "Invalid patient gender";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatDob.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient DOB";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (line.PatDob == "01/01/1900")
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (!DateTime.TryParse(line.PatDob, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The date of birth can't be later than today's date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateStart.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit Start Date";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateStart, out dtStart))
                    {
                        rejLine.RejectReason = "Invalid Visit Start Date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than today's date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateEnd.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit End Date";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateEnd, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Visit End Date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit end date can't be later than today's date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(dtResult) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than the end date";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Protocol.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Protocol number";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (ProtocolNotFound(line.Protocol))
                    {
                        rejLine.RejectReason = "Protocol not found in database";
                        ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Location.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No location";
                    ImportHelpers.CopyToRejectOutpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }

                //todo - more validation on location
                string mrn = ImportHelpers.ValidateMrn(line);
                line.PatMrn = mrn;

                acceptLines.Add(line);
            }
            dgvImport.DataSource = acceptLines;
            dgvReject.DataSource = rejList;
            dgvReject.Columns[0].Frozen = true;
            Cursor.Current = Cursors.Default;

        }

        private void RunRulesInpatient()
        {
            DateTime dtResult = new DateTime();
            DateTime dtStart = new DateTime();

            var lines = dgvImport.DataSource as SortableBindingList<ImportColumnsInpatient>;
            var acceptLines = new SortableBindingList<ImportColumnsInpatient>();
            var rejList = new SortableBindingList<ImportColumnsInpatientReject>();
            var rejLine = new ImportColumnsInpatientReject();

            int count = 0;
            int total = lines.Count;
            Cursor.Current = Cursors.WaitCursor;

            foreach (var line in lines)
            {
                count++;
                lblProgress.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();
                rejLine = new ImportColumnsInpatientReject();

                if (line.CcNum.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No confirmation number";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    int retVal = ConfirmationIsDuplicate(line.CcNum, ImportType.Inpatient);
                    if (retVal != 0)
                    {
                        if (retVal == 1)
                            rejLine.RejectReason = "Confirmation number already exists in the database";
                        else
                            rejLine.RejectReason = "ERROR:in Procedure - ConfirmationIsDuplicate";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }

                if (line.PatFirstName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient first name";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatLastName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient last name";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                string patientGender = line.PatGender.Trim();
                if (patientGender.Length == 0)
                {
                    rejLine.RejectReason = "No patient gender";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (!((patientGender == "M") || (patientGender == "MF") || (patientGender == "F") || (patientGender == "FM")))
                {
                    rejLine.RejectReason = "Invalid patient gender";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatDob.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient DOB";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (line.PatDob == "01/01/1900")
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (!DateTime.TryParse(line.PatDob, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The date of birth can't be later than today's date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.AdmitDate.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit Start Date";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.AdmitDate, out dtStart))
                    {
                        rejLine.RejectReason = "Invalid Visit Start Date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than today's date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DischargeDate.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit End Date";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DischargeDate, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Visit End Date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit end date can't be later than today's date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(dtResult) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than the end date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Protocol.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Protocol number";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (ProtocolNotFound(line.Protocol))
                    {
                        rejLine.RejectReason = "Protocol not found in database";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Location.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No location";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }

                //todo - more validation on location
                string mrn = ImportHelpers.ValidateMrn(line);
                line.PatMrn = mrn;

                acceptLines.Add(line);
            }
            dgvImport.DataSource = acceptLines;
            if (acceptLines.Count > 0)
                btnToDb.Enabled = true;
            else
            {
                btnToDb.Enabled = false;
            }

            SortableBindingList<ImportColumnsInpatientReject> rejListCurrent;
            //keep current rejected rows and add to the new ones
            if (dgvReject.RowCount > 0)
            {
                rejListCurrent = dgvReject.DataSource as SortableBindingList<ImportColumnsInpatientReject>;
                foreach (var item in rejListCurrent)
                {
                    rejList.Add(item);
                }
            }
            dgvReject.DataSource = rejList;
            dgvReject.Columns[0].Frozen = true;
            Cursor.Current = Cursors.Default;

        }

        private void RunRulesInpatientReject()
        {
            DateTime dtResult = new DateTime();
            DateTime dtStart = new DateTime();
            var lines = dgvReject.DataSource as SortableBindingList<ImportColumnsInpatientReject>;
            if (lines == null)
                return;
            var acceptLines = dgvImport.DataSource as SortableBindingList<ImportColumnsInpatient>;
            var rejList = new SortableBindingList<ImportColumnsInpatientReject>();
            var rejLine = new ImportColumnsInpatientReject();

            int count = 0;
            int total = lines.Count;
            Cursor.Current = Cursors.WaitCursor;

            foreach (var line in lines)
            {
                count++;
                lblProgress2.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();
                rejLine = new ImportColumnsInpatientReject();

                if (line.CcNum.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No confirmation number";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    int retVal = ConfirmationIsDuplicate(line.CcNum, ImportType.Inpatient);
                    if (retVal != 0)
                    {
                        if (retVal == 1)
                            rejLine.RejectReason = "Confirmation number already exists in the database";
                        else
                            rejLine.RejectReason = "ERROR:in Procedure - ConfirmationIsDuplicate";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }

                if (line.PatFirstName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient first name";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatLastName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient last name";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                string patientGender = line.PatGender.Trim();
                if (patientGender.Length == 0)
                {
                    rejLine.RejectReason = "No patient gender";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (!((patientGender == "M") || (patientGender == "MF") || (patientGender == "F") || (patientGender == "FM")))
                {
                    rejLine.RejectReason = "Invalid patient gender";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatDob.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient DOB";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (line.PatDob == "01/01/1900")
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (!DateTime.TryParse(line.PatDob, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The date of birth can't be later than today's date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.AdmitDate.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit Start Date";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.AdmitDate, out dtStart))
                    {
                        rejLine.RejectReason = "Invalid Visit Start Date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than today's date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DischargeDate.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit End Date";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DischargeDate, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Visit End Date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit end date can't be later than today's date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(dtResult) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than the end date";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Protocol.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Protocol number";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (ProtocolNotFound(line.Protocol))
                    {
                        rejLine.RejectReason = "Protocol not found in database";
                        ImportHelpers.CopyToRejectInpatient(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Location.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No location";
                    ImportHelpers.CopyToRejectInpatient(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }

                //todo - more validation on location
                string mrn = ImportHelpers.ValidateMrn(line);
                line.PatMrn = mrn;

                acceptLines.Add(line);
            }
            dgvImport.DataSource = acceptLines;
            dgvReject.DataSource = rejList;
            dgvReject.Columns[0].Frozen = true;
            Cursor.Current = Cursors.Default;

        }

        private void RunRulesUltraSound()
        {
            DateTime dtResult = new DateTime();
            DateTime dtStart = new DateTime();
            
            var lines = dgvImport.DataSource as SortableBindingList<ImportColumnsUltraSound>;
            var acceptLines = new SortableBindingList<ImportColumnsUltraSound>();
            var rejList = new SortableBindingList<ImportColumnsUltraSoundReject>();
            var rejLine = new ImportColumnsUltraSoundReject();

            int count = 0;
            int total = lines.Count;
            Cursor.Current = Cursors.WaitCursor;

            foreach (var line in lines)
            {
                count++;
                lblProgress.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();
                rejLine = new ImportColumnsUltraSoundReject();

                if (line.CcNum.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No confirmation number";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    int retVal = ConfirmationIsDuplicate(line.CcNum, ImportType.UltraSound);
                    if (retVal != 0)
                    {
                        if (retVal == 1)
                            rejLine.RejectReason = "Confirmation number already exists in the database";
                        else
                            rejLine.RejectReason = "ERROR:in Procedure - ConfirmationIsDuplicate";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }

                if (line.PatFirstName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient first name";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatLastName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient last name";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                string patientGender = line.PatGender.Trim();
                if (patientGender.Length == 0)
                {
                    rejLine.RejectReason = "No patient gender";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (!((patientGender == "M") || (patientGender == "MF") || (patientGender == "F") || (patientGender == "FM")))
                {
                    rejLine.RejectReason = "Invalid patient gender";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatDob.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient DOB";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (line.PatDob == "01/01/1900")
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (!DateTime.TryParse(line.PatDob, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The date of birth can't be later than today's date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateStart.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit Start Date";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateStart, out dtStart))
                    {
                        rejLine.RejectReason = "Invalid Visit Start Date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than today's date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateEnd.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit End Date";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateEnd, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Visit End Date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit end date can't be later than today's date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(dtResult) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than the end date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Protocol.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Protocol number";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (ProtocolNotFound(line.Protocol))
                    {
                        rejLine.RejectReason = "Protocol not found in database";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Location.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No location";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }

                //todo - more validation on location
                string mrn = ImportHelpers.ValidateMrn(line);
                line.PatMrn = mrn;

                acceptLines.Add(line);
            }
            dgvImport.DataSource = acceptLines;
            if (acceptLines.Count > 0)
                btnToDb.Enabled = true;
            else
            {
                btnToDb.Enabled = false;
            }

            SortableBindingList<ImportColumnsUltraSoundReject> rejListCurrent;
            //keep current rejected rows and add to the new ones
            if (dgvReject.RowCount > 0)
            {
                rejListCurrent = dgvReject.DataSource as SortableBindingList<ImportColumnsUltraSoundReject>;
                foreach (var item in rejListCurrent)
                {
                    rejList.Add(item);
                }
            }
            dgvReject.DataSource = rejList;
            dgvReject.Columns[0].Frozen = true;
            Cursor.Current = Cursors.Default;

        }

        private void RunRulesUltraSoundReject()
        {
            DateTime dtResult = new DateTime();
            DateTime dtStart = new DateTime();
            var lines = dgvReject.DataSource as SortableBindingList<ImportColumnsUltraSoundReject>;
            if (lines == null)
                return;
            var acceptLines = dgvImport.DataSource as SortableBindingList<ImportColumnsUltraSound>;
            var rejList = new SortableBindingList<ImportColumnsUltraSoundReject>();
            var rejLine = new ImportColumnsUltraSoundReject();

            int count = 0;
            int total = lines.Count;
            Cursor.Current = Cursors.WaitCursor;

            foreach (var line in lines)
            {
                count++;
                lblProgress2.Text = "Processing: " + count + " of " + total;
                Application.DoEvents();
                rejLine = new ImportColumnsUltraSoundReject();

                if (line.CcNum.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No confirmation number";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    int retVal = ConfirmationIsDuplicate(line.CcNum, ImportType.UltraSound);
                    if (retVal != 0)
                    {
                        if (retVal == 1)
                            rejLine.RejectReason = "Confirmation number already exists in the database";
                        else
                            rejLine.RejectReason = "ERROR:in Procedure - ConfirmationIsDuplicate";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }

                if (line.PatFirstName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient first name";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatLastName.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient last name";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                string patientGender = line.PatGender.Trim();
                if (patientGender.Length == 0)
                {
                    rejLine.RejectReason = "No patient gender";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (!((patientGender == "M") || (patientGender == "MF") || (patientGender == "F") || (patientGender == "FM")))
                {
                    rejLine.RejectReason = "Invalid patient gender";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                if (line.PatDob.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No patient DOB";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (line.PatDob == "01/01/1900")
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (!DateTime.TryParse(line.PatDob, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Date of birth";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The date of birth can't be later than today's date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateStart.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit Start Date";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateStart, out dtStart))
                    {
                        rejLine.RejectReason = "Invalid Visit Start Date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than today's date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.DateEnd.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Visit End Date";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (!DateTime.TryParse(line.DateEnd, out dtResult))
                    {
                        rejLine.RejectReason = "Invalid Visit End Date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtResult.CompareTo(DateTime.Today) > 0)
                    {
                        rejLine.RejectReason = "The visit end date can't be later than today's date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                    if (dtStart.CompareTo(dtResult) > 0)
                    {
                        rejLine.RejectReason = "The visit start date can't be later than the end date";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Protocol.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No Protocol number";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }
                else
                {
                    if (ProtocolNotFound(line.Protocol))
                    {
                        rejLine.RejectReason = "Protocol not found in database";
                        ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                        rejList.Add(rejLine);
                        continue;
                    }
                }
                if (line.Location.Trim().Length == 0)
                {
                    rejLine.RejectReason = "No location";
                    ImportHelpers.CopyToRejectUltraSound(rejLine, line);
                    rejList.Add(rejLine);
                    continue;
                }

                //todo - more validation on location
                string mrn = ImportHelpers.ValidateMrn(line);
                line.PatMrn = mrn;

                acceptLines.Add(line);
            }
            dgvImport.DataSource = acceptLines;
            dgvReject.DataSource = rejList;
            dgvReject.Columns[0].Frozen = true;
            Cursor.Current = Cursors.Default;

        }

        private bool ProtocolNotFound(string protocol)
        {
            int retVal = GetProtocolID(protocol);
            if (retVal < 1)
                return true;

            return false;
        }
                       
        private void btnRunRulesRejected_Click(object sender, EventArgs e)
        {
            btnRunRulesRejected.Enabled = false;
            this.Enabled = false;

            switch (_importType)
            {
                case ImportType.Outpatient:
                    RunRulesOutpatientReject();
                    break;
                case ImportType.Inpatient:
                    RunRulesInpatientReject();
                    break;
                case ImportType.UltraSound:
                    RunRulesUltraSoundReject();
                    break;
            }

            btnRunRulesRejected.Enabled = true;
            this.Enabled = true;

            _doesImportNeedValidation = false;
            
        }

        private void dgvImport_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
        {
            if (MessageBox.Show(this, "Are you sure you want to delete this row", "Confirm Delete Row", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Cancel)
            {
                e.Cancel = true;
                return;
            }
        }

        private void dgvImport_CurrentCellChanged(object sender, EventArgs e)
        {
            _doesImportNeedValidation = true;
        }

        private void dgvReject_CurrentCellChanged(object sender, EventArgs e)
        {
            //btnRunRulesRejected.Enabled = true;
        }

        private void dgvReject_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
        {
            if (MessageBox.Show(this, "Are you sure you want to delete this row", "Confirm Delete Row", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Cancel)
            {
                e.Cancel = true;
                return;
            }

        }

        private void dgvImport_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            btnToDb.Enabled = false;
        }
        
    }
}
